if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MigrateCaseSite]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].MigrateCaseSite
GO

CREATE PROCEDURE dbo.MigrateCaseSite
(
	@OldSiteID uniqueidentifier,
	@NewSiteID uniqueidentifier,
	@NewSiteCollectionID uniqueidentifier,
	@PageUrlWithNoQueryString nvarchar(1000)
)
AS
SET NOCOUNT ON

DECLARE
	@OldSiteUrl nvarchar(1000),
	@NewSiteUrl nvarchar(1000),
	@OldDictionaryUrlValue nvarchar(1000),
	@NewDictionaryUrlValue nvarchar(1000)
		
SELECT 
	@OldSiteUrl = OldSiteUrl,
	@NewSiteUrl = NewSiteUrl
FROM
	CaseSiteMapping
WHERE
	OldSiteID = @OldSiteID

UPDATE
	PreservationNoticeHistory
SET
	NewSiteID = @NewSiteID
WHERE
	SiteID = @OldSiteID
	
UPDATE
	SendNoticeQueue
SET
	NewSiteUrl = @NewSiteUrl
WHERE
	SiteUrl = @OldSiteUrl
	
UPDATE
	SendNoticeHistory
SET
	NewSiteUrl = @NewSiteUrl
WHERE
	SiteUrl = @OldSiteUrl
	
UPDATE
	UrlDictionary
SET
	--acknowledgmentUrl = "{0}://{1}/{2}/Acknowledge.aspx?case={3}&item={4}"
	NewUrlValue = REPLACE(REPLACE(UrlValue, SUBSTRING(UrlValue, 0, (CHARINDEX('case=', UrlValue, 0) -1)), @PageUrlWithNoQueryString), REPLACE(@OldSIteID, '-', ''), REPLACE(@NewSIteID, '-', '')) + '&siteCollection=' + REPLACE(@NewSiteCollectionID, '-', '')
WHERE
	SUBSTRING(UrlValue, (CHARINDEX('case=', UrlValue, 0) + 5), 32) = 
		REPLACE(@OldSiteID, '-', '')
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CaseSiteMappingInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].CaseSiteMappingInsert
GO

CREATE PROCEDURE dbo.CaseSiteMappingInsert
(
	@ParentPortalUrl nvarchar(1000),
	@OldSiteID uniqueidentifier,
	@NewSiteID uniqueidentifier,
	@OldSiteUrl nvarchar(1000),
	@NewSiteUrl nvarchar(1000)
)
AS
SET NOCOUNT ON

IF (SELECT COUNT(*) FROM CaseSiteMapping WHERE OldSiteID = @OldSiteID) = 0
BEGIN
	INSERT INTO CaseSiteMapping
	(
		ParentPortalUrl,
		OldSiteID, 
		NewSiteID, 
		OldSiteUrl, 
		NewSiteUrl
	)
	VALUES
	(
		@ParentPortalUrl,
		@OldSiteID, 
		@NewSiteID, 
		@OldSiteUrl, 
		@NewSiteUrl
	)
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CaseSiteMappingUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].CaseSiteMappingUpdate
GO

CREATE PROCEDURE dbo.CaseSiteMappingUpdate
(
	@OldSiteID uniqueidentifier,
	@NewSiteID uniqueidentifier,
	@NewSiteUrl nvarchar(1000)
)
AS
SET NOCOUNT ON

UPDATE
	CaseSiteMapping
SET
	NewSiteID = @NewSiteID, 
	NewSiteUrl = @NewSiteUrl
WHERE
	OldSiteID = @OldSiteID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CaseSiteMappingGetNotMigrated]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].CaseSiteMappingGetNotMigrated
GO

CREATE PROCEDURE dbo.CaseSiteMappingGetNotMigrated
AS
SET NOCOUNT ON
SELECT
	*
FROM
	CaseSiteMapping
WHERE
	OldSiteID IS NOT NULL AND
	NewSiteID IS NULL
	
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CaseSiteMappingGetMigrated]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].CaseSiteMappingGetMigrated
GO

CREATE PROCEDURE dbo.CaseSiteMappingGetMigrated
AS
SET NOCOUNT ON
SELECT
	*
FROM
	CaseSiteMapping
WHERE
	NewSiteID IS NOT NULL
	
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CaseSiteMappingGetByParentPortal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].CaseSiteMappingGetByParentPortal
GO

CREATE PROCEDURE dbo.CaseSiteMappingGetByParentPortal
(
	@ParentPortalUrl nvarchar(1000)
)
AS
SET NOCOUNT ON
SELECT
	*, IsDataImported = CASE WHEN OldSiteID IS NOT NULL AND NewSiteID IS NOT NULL THEN 1 ELSE 0 END
FROM
	CaseSiteMapping

WHERE
	ParentPortalUrl like @ParentPortalUrl + '%'
GO